CREATE VIEW [dbo].[vSoaItem]
AS
    SELECT  [p].[PRODUCT_CODE] AS [ItemId],
            [p].[PRODUCT_CODE] AS [ProductCode],
            [p].[TITLE] AS [Name],
            CAST(CASE WHEN [p].[WEB_DESC] IS NULL
                           OR [p].[WEB_DESC] LIKE '' THEN [p].[DESCRIPTION]
                      ELSE [p].[WEB_DESC]
                 END AS VARCHAR(MAX)) AS [Description],
            CAST(NULL AS VARCHAR(50)) AS [TaxCategory],
            [p].[PROD_TYPE] AS [ItemClass],
            [p].[IMAGE_URL] AS [ImageURL],
            [p].[THUMBNAIL_URL] AS [ThumbnailURL],
            [p].[RENEW_MONTHS],
            [p].[STOCK_ITEM],
            [p].[UNIT_OF_MEASURE],
            [p].[WEIGHT],
            [p].[TAXABLE],
            [p].[DECIMAL_POINTS],
            [p].[PRICE_1],
            [p].[PRICE_2],
            [p].[PRICE_3],
            [p].[COMPLIMENTARY],
            [p].[PST_TAXABLE],
            [p].[TAXABLE_VALUE],
            [p].[TAX_AUTHORITY],
            [p].[WEB_OPTION],
            [p].[IS_KIT],
            [p].[PREMIUM],
            [p].[FAIR_MARKET_VALUE],
            [p].[IS_FR_ITEM],
            [p].[APPEAL_CODE],
            [p].[CAMPAIGN_CODE],
            CASE
                WHEN [p].[PROD_TYPE] = 'MEETING' THEN
                    (SELECT TOP 1 [REG_CLASS_STORED_PROC]
                        FROM [dbo].[Meet_Master] [mm]
                        WHERE [mm].[MEETING] = [p].[PRODUCT_MAJOR])
                ELSE NULL
            END AS [RegistrantClassProcedure]
      FROM  [dbo].[Product] AS [p]
     WHERE  ( [p].[PROD_TYPE] IN ( 'SALES', 'GIFT', 'DUES', 'SUB', 'MISC',
                                   'CHAPT', 'SEC', 'VOL', 'CERTIFY', 'MEETING' ) )
    UNION
    SELECT  [ItemId],
            [ProductCode],
            [Name],
            [Description],
            [TaxCategory],
            [ItemClass],
            [ImageURL],
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL
      FROM  [dbo].[vSoaItemEvent]

GO